﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_REF_REPORT_Ref2010r_Get_Tour_Info_By_Seq')
	BEGIN
		DROP Procedure usp_UPDMS_REF_REPORT_Ref2010r_Get_Tour_Info_By_Seq
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_REF_REPORT_Ref2010r_Get_Tour_Info_By_Seq
**	Desc : 기행기 월별조회
**	Test Exec Query : Exec usp_UPDMS_REF_REPORT_Ref2010r_Get_Tour_Info_By_Seq 101
**	Called by : Ref_Dac_UPDMS_REF_REPORT_Ref2010r.cs
**	Program ID : Ref2010r
**	Auth : 송시명
**	Date : 2013-05-08
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_REF_REPORT_Ref2010r_Get_Tour_Info_By_Seq]
@li_seq int
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT urt.Shop_Id,
       urs.Shop_Nm,
       urt.Nick_Nm,
       urt.Tour_Dt,
       urt.Tour_Hour,
       urt.Tour_Minute,
       urt.Tour_Hour + ':' + urt.Tour_Minute + ' ~ '
         + CONVERT(CHAR(5), DATEADD(MI, Running_Time, CONVERT(DATETIME, urt.Tour_Dt + ' ' + urt.Tour_Hour + ':' + urt.Tour_Minute)), 8)
		 + ' (' + CONVERT(VARCHAR, urt.Running_Time) + ')' AS Tour_Time,
       urt.Place,
       urt.Price,
       urt.HomeRun_Yn,
	   CASE urt.HomeRun_Yn WHEN 'Y' THEN '<div class="sbtn sbtn_heart"></div>'
                           ELSE ''
       END HomeRun_Yn_Icon,
       urt.Satisfaction,
       urt.Contents,
	   LEN(urt.Contents) AS Contents_Word_Count,
       urt.Keyword,
	   LEN(urt.Keyword) AS Keyword_Word_Count,
       CASE urt.Latitude + urt.Longitude WHEN '' THEN ''
            ELSE '<div title="네이버맵" class="sbtn sbtn_map" onclick="goNaverMap(''' + urt.Latitude + ''',''' + urt.Longitude + ''');"></div>'
       END NaverMap_Link,
	   CASE WHEN dbo.ufn_UPDMS_Get_Exist_Attach_File_Image(urt.Seq, 'Ref1012m') <= 0 THEN ''
            ELSE '<div title="첨부파일" class="sbtn sbtn_fdisk" onclick="popupMultiImage(''' + CONVERT(VARCHAR, urt.Seq) + ''',''Ref1012m'');"></div>'
       END Attach_File_Info
  FROM UPDMS_REF_TOUR urt WITH(NOLOCK)
  JOIN UPDMS_REF_SHOP urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
 WHERE urt.Seq = @li_seq

GO